﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace VietKinsNutrition.Data
{
    public class ClsThucdon_monanDAL
    {
        ClsTblDm_NamhocDAL ClsTblDm_Namhocdal = new ClsTblDm_NamhocDAL();
        static string iNamhocId;
        public ClsThucdon_monanDAL()
        {
            iNamhocId = ClsTblDm_Namhocdal.ReturnNamhocId().ToString();
        }
        ClsBindingBLL ClsBin = new ClsBindingBLL();
        //ClsConnection ClsCnn = new ClsConnection();
        public void Del_Thucphamtheobua_DeleteByNamhocId(int NamhocId)
        {
            string NamePrc = "delete FROM         Thucphamtheobua WHERE     (NamhocId = " + NamhocId.ToString() + ") ";
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@NamhocId", NamhocId));
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);

        }
        public DataTable ThucdontuanSelectAllByKhoilopIdandDate(int KhoilopId, DateTime Value)
        {
            string NamePrc = "SELECT TblThu2.Id AS BuaantrongngayId, TblThu2.Tenbuaan, TblThu2.thu2, TblThu3.thu3, TblThu4.thu4, TblThu5.thu5, TblThu6.thu6, TblThu7.thu7 " +
       " FROM [SELECT TblDm_Buaantrongngay.Id, TblDm_Buaantrongngay.Tenbuaan, TblThucdontuan.Tenmonan AS thu7 " +
       " FROM TblDm_Buaantrongngay LEFT JOIN (SELECT TblThucdon_monan.Id, TblThucdon_monan.ThuvienmonanId, TblThucdon_monan.Ngay, TblThucdon_monan.BuaantrongngayId, TblThucdon_monan.KhoilopId, TblThucdon_monan.NamhocId, TblThucdon_monan.Tenmonan " +
       "  FROM TblThucdon_monan WHERE ( (TblThucdon_monan.Ngay)=DateAdd (\"d\", 5, #" + Value.ToString("MM/dd/yyyy") + "#)  AND (TblThucdon_monan.KhoilopId)=" + KhoilopId + ") ) AS TblThucdontuan ON TblDm_Buaantrongngay.Id = TblThucdontuan.BuaantrongngayId]. AS TblThu7 INNER JOIN ([SELECT TblDm_Buaantrongngay.Id, TblDm_Buaantrongngay.Tenbuaan, TblThucdontuan.Tenmonan AS thu6 " +
       " FROM TblDm_Buaantrongngay LEFT JOIN (SELECT TblThucdon_monan.Id, TblThucdon_monan.ThuvienmonanId, TblThucdon_monan.Ngay, TblThucdon_monan.BuaantrongngayId, TblThucdon_monan.KhoilopId, TblThucdon_monan.NamhocId, TblThucdon_monan.Tenmonan " +
   " FROM TblThucdon_monan WHERE ( (TblThucdon_monan.Ngay)=DateAdd (\"d\",4, #" + Value.ToString("MM/dd/yyyy") + "#)  AND (TblThucdon_monan.KhoilopId)=" + KhoilopId + ") ) AS TblThucdontuan ON TblDm_Buaantrongngay.Id = TblThucdontuan.BuaantrongngayId]. AS TblThu6 INNER JOIN ([SELECT TblDm_Buaantrongngay.Id, TblDm_Buaantrongngay.Tenbuaan, TblThucdontuan.Tenmonan AS thu5 " +
       " FROM TblDm_Buaantrongngay LEFT JOIN (SELECT TblThucdon_monan.Id, TblThucdon_monan.ThuvienmonanId, TblThucdon_monan.Ngay, TblThucdon_monan.BuaantrongngayId, TblThucdon_monan.KhoilopId, TblThucdon_monan.NamhocId, TblThucdon_monan.Tenmonan " +
       " FROM TblThucdon_monan WHERE ( (TblThucdon_monan.Ngay)=DateAdd (\"d\", 3, #" + Value.ToString("MM/dd/yyyy") + "#)  AND (TblThucdon_monan.KhoilopId)=" + KhoilopId + ") ) AS TblThucdontuan ON TblDm_Buaantrongngay.Id = TblThucdontuan.BuaantrongngayId]. AS TblThu5 INNER JOIN ([SELECT TblDm_Buaantrongngay.Id, TblDm_Buaantrongngay.Tenbuaan, TblThucdontuan.Tenmonan AS thu4 " +
   " FROM TblDm_Buaantrongngay LEFT JOIN (SELECT TblThucdon_monan.Id, TblThucdon_monan.ThuvienmonanId, TblThucdon_monan.Ngay, TblThucdon_monan.BuaantrongngayId, TblThucdon_monan.KhoilopId, TblThucdon_monan.NamhocId, TblThucdon_monan.Tenmonan " +
   " FROM TblThucdon_monan WHERE ( (TblThucdon_monan.Ngay)=DateAdd (\"d\", 2, #" + Value.ToString("MM/dd/yyyy") + "#)  AND (TblThucdon_monan.KhoilopId)=" + KhoilopId + ") ) AS TblThucdontuan ON TblDm_Buaantrongngay.Id = TblThucdontuan.BuaantrongngayId]. AS TblThu4 INNER JOIN ([SELECT TblDm_Buaantrongngay.Id, TblDm_Buaantrongngay.Tenbuaan, TblThucdontuan.Tenmonan AS thu3 " +
   " FROM TblDm_Buaantrongngay LEFT JOIN (SELECT TblThucdon_monan.Id, TblThucdon_monan.ThuvienmonanId, TblThucdon_monan.Ngay, TblThucdon_monan.BuaantrongngayId, TblThucdon_monan.KhoilopId, TblThucdon_monan.NamhocId, TblThucdon_monan.Tenmonan " +
   " FROM TblThucdon_monan WHERE ( (TblThucdon_monan.Ngay)=DateAdd (\"d\", 1, #" + Value.ToString("MM/dd/yyyy") + "#)  AND (TblThucdon_monan.KhoilopId)=" + KhoilopId + ") ) AS TblThucdontuan ON TblDm_Buaantrongngay.Id = TblThucdontuan.BuaantrongngayId]. AS TblThu3 INNER JOIN [SELECT TblDm_Buaantrongngay.Id, TblDm_Buaantrongngay.Tenbuaan, TblThucdontuan.Tenmonan AS thu2 " +
   " FROM TblDm_Buaantrongngay LEFT JOIN (SELECT TblThucdon_monan.Id, TblThucdon_monan.ThuvienmonanId, TblThucdon_monan.Ngay, TblThucdon_monan.BuaantrongngayId, TblThucdon_monan.KhoilopId, TblThucdon_monan.NamhocId, TblThucdon_monan.Tenmonan " +
   " FROM TblThucdon_monan WHERE ( (TblThucdon_monan.Ngay)=#" + Value.ToString("MM/dd/yyyy") + "#  AND (TblThucdon_monan.KhoilopId)=" + KhoilopId + ") ) AS TblThucdontuan ON TblDm_Buaantrongngay.Id = TblThucdontuan.BuaantrongngayId]. AS TblThu2 ON TblThu3.Id = TblThu2.Id) ON TblThu4.Id = TblThu2.Id) ON TblThu5.Id = TblThu2.Id) ON TblThu6.Id = TblThu2.Id) ON TblThu7.Id = TblThu2.Id ";
            string NameTable = "TblDm_Buaantrongngay";
            //DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;


            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            //Dset.Tables.Add(NewTable);
            ClsBindingBLL.OleDBSelectAll(NamePrc).Fill(NewTable);
            //BindingSource Binding = new BindingSource(Dset, NameTable);
            //DataTable Table = Dset.Tables[NameTable];
            return NewTable;
        }
        public BindingSource ThucdontuanSelectAllByBeginDateToEndDatime(List<DateTime> BeginDate, List<DateTime> EndDate)
        {
            string NamePrc = "";
            for (int i = 0; i < BeginDate.Count; i++)
            {
                if (i == 0)
                {
                    NamePrc = NamePrc + "SELECT top 1 IIF( isnull(TblThucdon_monan.Ngay),null,#" + BeginDate[i].ToString("MM/dd/yyyy") + "#) AS Tungay,  IIF( isnull(TblThucdon_monan.Ngay),null,#" + EndDate[i].ToString("MM/dd/yyyy") + "#) AS  Denngay " +
                       " FROM TblThucdon_monan WHERE TblThucdon_monan.Ngay>=#" + BeginDate[i].ToString("MM/dd/yyyy") + "# AND TblThucdon_monan.Ngay<=#" + EndDate[i].ToString("MM/dd/yyyy") + "# ";
                }
                else
                {
                    NamePrc = NamePrc + " union all " + " SELECT top 1 IIF( isnull(TblThucdon_monan.Ngay),null,#" + BeginDate[i].ToString("MM/dd/yyyy") + "#) AS Tungay,  IIF( isnull(TblThucdon_monan.Ngay),null,#" + EndDate[i].ToString("MM/dd/yyyy") + "#) AS  Denngay " +
                       " FROM TblThucdon_monan WHERE TblThucdon_monan.Ngay>=#" + BeginDate[i].ToString("MM/dd/yyyy") + "# AND TblThucdon_monan.Ngay<=#" + EndDate[i].ToString("MM/dd/yyyy") + "# ";
                }
            }
            string NameTable = "TblDm_Buaantrongngay";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;


            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);
            ClsBindingBLL.OleDBSelectAll(NamePrc).Fill(Dset.Tables[NameTable]);
            BindingSource Binding = new BindingSource(Dset, NameTable);
            return Binding;
        }
        public DataSet ThucdontuanSelectReturnEndDatime()
        {
            string NamePrc = "SELECT top 1 ngay   FROM TblThucdon_monan ORDER BY ngay ASC  ";
            string NameTable = "TblDm_Buaantrongngay";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;


            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);
            ClsBindingBLL.OleDBSelectAll(NamePrc).Fill(Dset.Tables[NameTable]);
            BindingSource Binding = new BindingSource(Dset, NameTable);
            return Dset;
        }



        public DataSet DataSet_ThucdontuanSelectAllByBeginDateToEndDatime(List<DateTime> BeginDate, List<DateTime> EndDate)
        {
            string NamePrc = "";
            for (int i = 0; i < BeginDate.Count; i++)
            {
                if (i == 0)
                {
                    NamePrc = NamePrc + "SELECT top 1 IIF( isnull(TblThucdon_monan.Ngay),null,#" + BeginDate[i].ToShortDateString() + "#) AS Tungay,  IIF( isnull(TblThucdon_monan.Ngay),null,#" + EndDate[i].ToString("MM/dd/yyyy") + "#) AS  Denngay " +
                       " FROM TblThucdon_monan WHERE TblThucdon_monan.Ngay>=#" + BeginDate[i].ToString("MM/dd/yyyy") + "# AND TblThucdon_monan.Ngay<=#" + EndDate[i].ToString("MM/dd/yyyy") + "# ";
                }
                else
                {
                    NamePrc = NamePrc + " union all " + " SELECT top 1 IIF( isnull(TblThucdon_monan.Ngay),null,#" + BeginDate[i].ToShortDateString() + "#) AS Tungay,  IIF( isnull(TblThucdon_monan.Ngay),null,#" + EndDate[i].ToString("MM/dd/yyyy") + "#) AS  Denngay " +
                       " FROM TblThucdon_monan WHERE TblThucdon_monan.Ngay>=#" + BeginDate[i].ToString("MM/dd/yyyy") + "# AND TblThucdon_monan.Ngay<=#" + EndDate[i].ToString("MM/dd/yyyy") + "# ";
                }
            }
            string NameTable = "TblDm_Buaantrongngay";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;


            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);
            ClsBindingBLL.OleDBSelectAll(NamePrc).Fill(Dset.Tables[NameTable]);

            return Dset;
        }

        public void Del_TblThucdon_monan_DeleteByNamhocId(int NamhocId)
        {
            string NamePrc = "DELETE FROM TblThucdon_monan  WHERE NamhocId =" + NamhocId.ToString();
            OleDbCommand Cmm = new OleDbCommand();
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);

        }
        //
        public DataSet Thucpham_thucan_FindNameSamePrc(int NhomthucanId, string VarTen)
        {
            string NamePrc = QueryString.TBLTHUCPHAM_THUCAN_FINDNAMESAMEPRC;

            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            // NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@NhomthucanId", NhomthucanId));

            Cmm.Parameters.Add(new OleDbParameter("@VarTen", VarTen));

            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            OleDbDataAdapter Dap;
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[0]);

            return Dset;
        }
        //[][]
        public void TblThucdon_monan_Del_ByNgay_KhoilopId_BuaantrongngayId(DateTime Ngay, int BuaantrongngayId, int KhoilopId)
        {
            string NamePrc = "delete FROM         TblThucdon_monan  WHERE     (Ngay = #" + Ngay.ToString("MM/dd/yyyy") + "#) AND (KhoilopId = " + KhoilopId.ToString() + ") AND (BuaantrongngayId = " + BuaantrongngayId.ToString() + " and NamhocId =" + ClsTblDm_Namhocbll.ReturnNamhocId() + ") ";
            OleDbCommand Cmm = new OleDbCommand();
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }

        private string date;
        public DataTable SelectByNgayanKhoilop(DateTime Ngay, int KhoilopId)
        {
            date = Ngay.ToString("MM/dd/yyyy");
            string NamePrc = "SELECT  (select Tenbuaan from TblDm_Buaantrongngay where  Id = TblThucdon_monan.BuaantrongngayId ) AS [Buaan] ,TblThucdon_monan.Tenmonan " +
"  FROM TblThucdon_monan WHERE TblThucdon_monan.Ngay=#" + Ngay.ToString("MM/dd/yyyy") + "# AND TblThucdon_monan.NamhocId=" + iNamhocId + " and TblThucdon_monan.KhoilopId =" + KhoilopId.ToString() + " ";
            string NameTable = "TblDm_Buaantrongngay";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;
            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);

            Dset.Tables.Add(NewTable);

            ClsBindingBLL.OleDBSelectAll(NamePrc).Fill(Dset.Tables[NameTable]);

            BindingSource Binding = new BindingSource(Dset, NameTable);

            return Dset.Tables[0];

        }

        public bool SelectByNgayNX(DateTime Ngay)
        {
            string NamePrc = "SELECT MAX(NgayNX) AS NgayNX FROM TblHoadonnhapxuat";
            string NameTable = "TblDm_HoaDonNhapXuatByNgay";
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;
            DateTime NgayNX = DateTime.Now;

            ClsBindingBLL.OleDBSelectAll(NamePrc).Fill(NewTable);
            foreach (DataRow dr in NewTable.Rows)
            {
                NgayNX = (DateTime)dr["NgayNX"];
            }
            if (NgayNX > Ngay)
            {
                return true;
            }
            return false;

        }

        public DataTable SelectBy(DateTime Ngay)
        {
            string NamePrc = "SELECT (select Tenkhoilop from TblDm_Khoilop where Id = TblThucdon_monan.KhoilopId) as Tenkhoilop, (select Tenbuaan from TblDm_Buaantrongngay where  Id = TblThucdon_monan.BuaantrongngayId ) AS [Bữa ăn] ,TblThucdon_monan.Tenmonan " +
"  FROM TblThucdon_monan WHERE TblThucdon_monan.Ngay=#" + Ngay.ToString("MM/dd/yyyy")  + "# AND TblThucdon_monan.NamhocId=" + iNamhocId + " ";
            string NameTable = "TblDm_Buaantrongngay";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;
            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);

            Dset.Tables.Add(NewTable);

            ClsBindingBLL.OleDBSelectAll(NamePrc).Fill(Dset.Tables[NameTable]);

            BindingSource Binding = new BindingSource(Dset, NameTable);

            return Dset.Tables[0];

        }
        public DataTable TblThucdon_monan_ByNgay_KhoilopId_BuaantrongngayId(DateTime Ngay, int BuaantrongngayId, int KhoilopId)
        {
            DataSet Set = new DataSet();
            DataTable NewTable = new DataTable();
            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Set.Tables.Add(NewTable);

            QueryString.SPROC_TBLTHUCDON_MONAN_BYNGAY_KHOILOPID_BUAANTRONGNGAYID = ""
            + "SELECT TblDm_Thuvienmonan.Tenmonan, TblDm_Khoilop.Tenkhoilop, TblDm_Buaantrongngay.Tenbuaan, "
            + "       TblThucdon_monan.ThuvienmonanId, TblThucdon_monan.Ngay, TblThucdon_monan.BuaantrongngayId,  "
            + "       TblThucdon_monan.KhoilopId "
            + " FROM ((TblThucdon_monan INNER JOIN TblDm_Thuvienmonan ON TblThucdon_monan.ThuvienmonanId = TblDm_Thuvienmonan.Id) "
            + "   INNER JOIN TblDm_Khoilop ON TblThucdon_monan.KhoilopId = TblDm_Khoilop.Id)"
            + "   INNER JOIN TblDm_Buaantrongngay ON TblThucdon_monan.BuaantrongngayId = TblDm_Buaantrongngay.Id "
            + " WHERE     TblThucdon_monan.Ngay = #" + Ngay.ToString("MM/dd/yyyy") + "# AND TblThucdon_monan.KhoilopId = " + KhoilopId + " AND TblThucdon_monan.BuaantrongngayId = " + BuaantrongngayId + " ";
            string NamePrc = QueryString.SPROC_TBLTHUCDON_MONAN_BYNGAY_KHOILOPID_BUAANTRONGNGAYID;
            OleDbCommand Cmm = new OleDbCommand();
            ClsConnection.OleDbDAdpter(NamePrc, Cmm).Fill(Set.Tables[0]);
            return Set.Tables[0];
        }
        ClsTblDm_NamhocBLL ClsTblDm_Namhocbll = new ClsTblDm_NamhocBLL();
        public void Add(int ThuvienmonanId, DateTime Ngay, int BuaantrongngayId, int KhoilopId)
        {
            string NamePrc = "	INSERT INTO TblThucdon_monan(ThuvienmonanId, Ngay, BuaantrongngayId, KhoilopId, NamhocId) VALUES (" + ThuvienmonanId.ToString() + ",#" + Ngay.ToString("MM/dd/yyyy") + "#," + BuaantrongngayId.ToString() + "," + KhoilopId.ToString() + "," + ClsTblDm_Namhocbll.ReturnNamhocId().ToString() + ") ";
            OleDbCommand Cmm = new OleDbCommand();
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
        public DataTable TblThucdon_monanByVar(DateTime Ngay, int BuaantrongngayId, int KhoilopId)
        {
            DataTable Tbl = new DataTable("Tbl");
            string NamePrc = " select Id from TblThucdon_monan where  Ngay =#" + Ngay.ToString("MM/dd/yyyy") + "# and  BuaantrongngayId=" + BuaantrongngayId.ToString() + " and  KhoilopId =" + KhoilopId.ToString() + "  and NamhocId =" + ClsTblDm_Namhocbll.ReturnNamhocId().ToString();
            OleDbCommand Cmm = new OleDbCommand();
            ClsConnection.OleDbDAdpter(NamePrc, Cmm).Fill(Tbl);
            return Tbl;
        }
        public void Add(DateTime Ngay, int BuaantrongngayId, int KhoilopId, string Tenmonan)
        {
            string NamePrc = "	INSERT INTO TblThucdon_monan(Tenmonan, Ngay, BuaantrongngayId, KhoilopId, NamhocId) VALUES ('" + Tenmonan.ToString() + "',#" + Ngay.ToString("MM/dd/yyyy") + "#," + BuaantrongngayId.ToString() + "," + KhoilopId.ToString() + "," + ClsTblDm_Namhocbll.ReturnNamhocId().ToString() + ") ";
            OleDbCommand Cmm = new OleDbCommand();
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
        public DataSet Bind_Khoilop()
        {
            DataSet Set = new DataSet();
            DataTable NewTable = new DataTable();
            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Set.Tables.Add(NewTable);
            string NamePrc = QueryString.SP_KHOILOP_GET;
            OleDbCommand Cmm = new OleDbCommand();
            ClsConnection.OleDbDAdpter(NamePrc, Cmm).Fill(Set.Tables[0]);
            return Set;
        }
        public DataSet Bind_Khoilop(int KhoilopId)
        {
            DataSet Set = new DataSet();
            DataTable NewTable = new DataTable();
            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Set.Tables.Add(NewTable);
            string NamePrc = "SELECT Id, Tenkhoilop FROM TblDm_Khoilop where Id = " + KhoilopId + " ";
            OleDbCommand Cmm = new OleDbCommand();
            ClsConnection.OleDbDAdpter(NamePrc, Cmm).Fill(Set.Tables[0]);
            return Set;
        }

        public DataTable SeletByBeginNgayToNgay(DateTime BeginNgay, DateTime ToNgay)
        {
            DataSet Set = new DataSet();
            DataTable NewTable = new DataTable();
            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Set.Tables.Add(NewTable);
            string NamePrc = QueryString.SPROC_TBLTHUCDON_MONAN_BYBEGINNGAYENDNGAY;
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@Begingay", BeginNgay.ToString("MM/dd/yyyy")));
            Cmm.Parameters.Add(new OleDbParameter("@EndNgay", ToNgay.ToString("MM/dd/yyyy")));
            ClsConnection.OleDbDAdpter(NamePrc, Cmm).Fill(Set.Tables[0]);
            return Set.Tables[0];
        }
        public DataTable SeletByNgay(DateTime Ngay)
        {
            DataSet Set = new DataSet();
            DataTable NewTable = new DataTable();
            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Set.Tables.Add(NewTable);
            string NamePrc = " SELECT Tenmonan ,(select Tenkhoilop from TblDm_khoilop where Id = Tblthucdon_monan.KhoilopId) AS Tenkhoilop, " +
           " (select Tenbuaan from TblDm_Buaantrongngay  where Id = TblThucdon_monan.BuaantrongngayId) AS Tenbuaan " +
           " , TblThucdon_monan.ThuvienmonanId, TblThucdon_monan.Ngay, TblThucdon_monan.BuaantrongngayId, TblThucdon_monan.KhoilopId " +
            " FROM TblThucdon_monan WHERE TblThucdon_monan.Ngay=#" + Ngay.ToString("MM/dd/yyyy") + "#  and TblThucdon_monan.NamhocId =" + iNamhocId.ToString() + "";
            OleDbCommand Cmm = new OleDbCommand();
            ClsConnection.OleDbDAdpter(NamePrc, Cmm).Fill(Set.Tables[0]);
            return Set.Tables[0];
        }
        public void Del(DateTime Ngay)
        {
            string NamePrc = QueryString.SPROC_TBLTHUCDON_MONAN_DELETE;
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@Ngay", Ngay.ToString("MM/dd/yyyy")));
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
        //
        public DataTable SeletByBuaantrongngayId(int BuaantrongngayId)
        {
            DataSet Set = new DataSet();
            DataTable NewTable = new DataTable();
            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Set.Tables.Add(NewTable);
            string NamePrc = QueryString.TBLTHUCDON_MONANBYBUAANTRONGNGAYID;
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@BuaantrongngayId", BuaantrongngayId));
            ClsConnection.OleDbDAdpter(NamePrc, Cmm).Fill(Set.Tables[0]);
            return Set.Tables[0];
        }
        
        public static DataTable GetByDate4Report(DateTime dateReport)
        {
            QueryString.SP_VWTHUCDON_GETBYWEEK4REPORT = " 	SELECT     TblThucdon_monan.Id, TblThucdon_monan.KhoilopId, TblDm_Khoilop.Tenkhoilop, TblThucdon_monan.BuaantrongngayId,  "
            + "                       TblDm_Buaantrongngay.Tenbuaan, TblThucdon_monan.ThuvienmonanId, TblDm_Thuvienmonan.Tenmonan,  "
            + "                       TblThucdon_monan.Ngay "
            + " 	FROM         ((TblThucdon_monan INNER JOIN "
            + " 						  TblDm_Khoilop ON TblThucdon_monan.KhoilopId = TblDm_Khoilop.Id) INNER JOIN "
            + " 						  TblDm_Thuvienmonan ON TblThucdon_monan.ThuvienmonanId = TblDm_Thuvienmonan.Id) INNER JOIN "
            + " 						  TblDm_Buaantrongngay ON TblThucdon_monan.BuaantrongngayId = TblDm_Buaantrongngay.Id "
            + " 	WHERE  datediff('d',Ngay,#" + dateReport.ToString("MM/dd/yyyy") + "#)=0 ";


            QueryString.SP_VWTHUCDON_GETBYWEEK4REPORT = " SELECT TblThucdon_monan.Id, TblThucdon_monan.KhoilopId, TblDm_Khoilop.Tenkhoilop, TblThucdon_monan.BuaantrongngayId, TblDm_Buaantrongngay.Tenbuaan, TblThucdon_monan.Tenmonan, TblThucdon_monan.Ngay " +
"  FROM TblDm_Buaantrongngay INNER JOIN (TblDm_Khoilop INNER JOIN TblThucdon_monan ON TblDm_Khoilop.Id = TblThucdon_monan.KhoilopId) ON TblDm_Buaantrongngay.Id = TblThucdon_monan.BuaantrongngayId " +
" WHERE (((TblThucdon_monan.Ngay)=#" + dateReport.ToString("MM/dd/yyyy") + "#)); ";
            DataTable NewTable = new DataTable();
            string NamePrc = QueryString.SP_VWTHUCDON_GETBYWEEK4REPORT;
            System.Data.OleDb.OleDbCommand Cmm = new System.Data.OleDb.OleDbCommand();
            ClsConnection.OleDbDAdpter(NamePrc, Cmm).Fill(NewTable);
            return NewTable;
        }

        public static DataTable GetByWeek4Report(DateTime startDate, DateTime endDate)
        {
            QueryString.SP_VWTHUCDON_GETBYWEEK4REPORT = "SELECT TblThucdon_monan.Id, TblThucdon_monan.KhoilopId, TblDm_Khoilop.Tenkhoilop, TblThucdon_monan.BuaantrongngayId,  "
            + " TblDm_Buaantrongngay.Tenbuaan, TblThucdon_monan.ThuvienmonanId, TblDm_Thuvienmonan.Tenmonan, TblThucdon_monan.Ngay "
            + " FROM ((TblThucdon_monan INNER JOIN TblDm_Khoilop ON TblThucdon_monan.KhoilopId = TblDm_Khoilop.Id) INNER JOIN "
            + "      TblDm_Thuvienmonan ON TblThucdon_monan.ThuvienmonanId = TblDm_Thuvienmonan.Id) INNER JOIN "
            + "      TblDm_Buaantrongngay ON TblThucdon_monan.BuaantrongngayId = TblDm_Buaantrongngay.Id "
            + " WHERE Ngay between #" + startDate.ToString("MM/dd/yyyy") + "# and #" + endDate.ToString("MM/dd/yyyy") + "# ";

            DataTable NewTable = new DataTable();
            string NamePrc = QueryString.SP_VWTHUCDON_GETBYWEEK4REPORT;
            string sql = " SELECT Tenmonan ,(select Tenkhoilop from TblDm_khoilop where Id = Tblthucdon_monan.KhoilopId) AS Tenkhoilop, " +
       " (select Tenbuaan from TblDm_Buaantrongngay  where Id = TblThucdon_monan.BuaantrongngayId) AS Tenbuaan " +
       " , TblThucdon_monan.ThuvienmonanId, TblThucdon_monan.Ngay, TblThucdon_monan.BuaantrongngayId, TblThucdon_monan.KhoilopId " +
        " FROM TblThucdon_monan WHERE Ngay between #" + startDate.ToString("MM/dd/yyyy") + "# and #" + endDate.ToString("MM/dd/yyyy") + "# ";
            System.Data.OleDb.OleDbCommand Cmm = new System.Data.OleDb.OleDbCommand();
            ClsConnection.OleDbDAdpter(sql, Cmm).Fill(NewTable);
            return NewTable;
        }
    }
}
